package com.irdstudio.apicenter.console.dm;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Component;

import com.irdstudio.apicenter.frame.core.jdbc.util.ConnectionManager;

@Component(value = "appDeviceRegisterDao")
public class AppDeviceRegisterDao {

	/**
	 * 根据主键查询单条记录
	 * 
	 * @param
	 * @return
	 * @throws SQLException
	 */
	public AppDeviceRegister queryWithKeys(String appId, String deviceId)
			throws SQLException {
		AppDeviceRegister dc = null;
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn
					.prepareStatement("SELECT * FROM app_device_register WHERE app_id=? and device_id=?");
			ps.setString(1, appId);
			ps.setString(2, deviceId);
			rs = ps.executeQuery();
			if (rs.next()) {
				dc = new AppDeviceRegister();
				dc.setDeviceId(rs.getString("device_id"));
				dc.setDeviceType(rs.getString("device_type"));
				dc.setAppId(rs.getString("app_id"));
				dc.setAppUserid(rs.getString("app_userid"));
				dc.setMobilePhone(rs.getString("mobile_phone"));
				dc.setIsAllow(rs.getString("is_allow"));

			}
		} catch (SQLException e) {
			throw new SQLException("queryAppDeviceRegisterWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close2(rs, conn, ps);
		}
		return dc;
	}

	/**
	 * 插入单条记录
	 * 
	 * @param
	 * @return
	 * @throws SQLException
	 */
	public int save(AppDeviceRegister appDeviceRegister) throws SQLException {

		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		ResultSet rs = null;
		int flag;
		try {
			// 通过app_id和app_userid从用户表查出电话号码
			ps = conn
					.prepareStatement("select * from app_user where app_id=? and app_userid=?");
			ps.setString(1, appDeviceRegister.getAppId());
			ps.setString(2, appDeviceRegister.getAppUserid());
			rs = ps.executeQuery();
			if (rs.next()) {
				appDeviceRegister.setMobilePhone(rs.getString("mobile_phone"));
			}
			// 将组合后的记录插入
			String sql2 = "insert into app_device_register values(?,?,?,?,?,?,?,?);";
			ps2 = conn.prepareStatement(sql2);
			ps2.setString(1, appDeviceRegister.getDeviceId());
			ps2.setString(2, appDeviceRegister.getPushDeviceId());
			ps2.setString(3, appDeviceRegister.getDeviceModel());
			ps2.setString(4, appDeviceRegister.getDeviceType());
			ps2.setString(5, appDeviceRegister.getAppId());
			ps2.setString(6, appDeviceRegister.getAppUserid());
			ps2.setString(7, appDeviceRegister.getMobilePhone());
			ps2.setString(8, appDeviceRegister.getIsAllow());
			flag = ps2.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("insertAppDeviceRegister is Wrong!"
					+ e.getMessage());
		} finally {
			close1(conn, ps);
		}
		return flag;

	}

	/**
	 * 修改模块参数
	 * 
	 * @param appModuleParam
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public int update(AppDeviceRegister appDeviceRegister) throws SQLException,
			ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int flag;
		try {
			String sql = "update  app_device_register  set is_allow=? where device_id=? and app_id=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, appDeviceRegister.getIsAllow());
			ps.setString(2, appDeviceRegister.getDeviceId());
			ps.setString(3, appDeviceRegister.getAppId());
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("updateAppDeviceRegisterWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close1(conn, ps);
		}
		return flag;

	}

	/**
	 * 删除模块参数
	 * 
	 * @param appModuleParam
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public int delete(AppDeviceRegister appDeviceRegister) throws SQLException,
			ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int flag;
		try {
			String sql = "delete from app_device_register where DEVICE_ID=? and APP_ID=? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, appDeviceRegister.getDeviceId());
			ps.setString(2, appDeviceRegister.getAppId());
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("deleteAppDeviceRegisterWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close1(conn, ps);
		}
		return flag;

	}
	
	/**
	 * 根据主键查询单条记录
	 * 
	 * @param
	 * @return
	 * @throws SQLException
	 */
	public List<AppDeviceRegister> queryWithByProvince(String appId)
			throws SQLException {
		AppDeviceRegister dc = null;
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<AppDeviceRegister> list = new ArrayList<AppDeviceRegister>();
		try {
			ps = conn
					.prepareStatement("SELECT a.province_name,COUNT(province_name) amount FROM app_device_register a where a.app_id=? GROUP BY a.province_name ORDER BY amount desc");
			ps.setString(1, appId);
			rs = ps.executeQuery();
			while (rs.next()) {
				dc = new AppDeviceRegister();
				//dc.setDeviceId(rs.getString("device_id"));
				//dc.setDeviceType(rs.getString("device_type"));
				//dc.setAppId(rs.getString("app_id"));
				//dc.setAppUserid(rs.getString("app_userid"));
				//dc.setMobilePhone(rs.getString("mobile_phone"));
				//dc.setIsAllow(rs.getString("is_allow"));
				dc.setProvinceName(rs.getString("province_name"));
				dc.setAmount(Integer.parseInt(rs.getString("amount")));
				list.add(dc);
			}
		} catch (SQLException e) {
			throw new SQLException("queryWithByProvince is Wrong!"
					+ e.getMessage());
		} finally {
			close2(rs, conn, ps);
		}
		return list;
	}
	
	/**
	 * 查询指定工程下前九个最多的交易   
	 * 
	 * @param appId    适用于MYSQL数据库
	 * @return
	 * @throws SQLException
	 */
	public List<AppTradeLog> queryWithBytradeCode(String appId)
			throws SQLException {
		List<AppTradeLog> dcList = new ArrayList<AppTradeLog>();
		PreparedStatement ps = null;
		Connection conn = ConnectionManager.getConnection();
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("SELECT a.app_id,a.TRADE_CODE,a.TRADE_NAME,COUNT(a.trade_code) count FROM app_trade_log a " + 
					" where a.app_id=? GROUP BY a.trade_code ORDER BY count DESC LIMIT 0,9 ");
			ps.setString(1, appId);
			rs = ps.executeQuery();
			while (rs.next()) {
				AppTradeLog dc = new AppTradeLog();
				dc.setAppId(rs.getString("APP_ID"));
				dc.setTradeCode(rs.getString("trade_code"));
				dc.setTradeName(rs.getString("trade_name"));
				dc.setAmount(Integer.parseInt(rs.getString("count")));
				dcList.add(dc);
			}
		} catch (SQLException e) {
			throw new SQLException("queryWithBytradeCode is Wrong!"
					+ e.getMessage());
		} finally {
			close(rs, null, ps);
		}
		return dcList;
	}
	//适用Oracle数据库
	public List<AppTradeLog> queryWithBytradeCodeOracl(String appId)
			throws SQLException {
		List<AppTradeLog> dcList = new ArrayList<AppTradeLog>();
		PreparedStatement ps = null;
		Connection conn = ConnectionManager.getConnection();
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("with tmp as (select tt.TRADE_CODE,tt.nm,tt.nn from (select t.TRADE_CODE,t. nm,  row_number() over(order by t.nm desc) nn  from "+
" (SELECT a.TRADE_CODE,count(a.trade_code) nm FROM app_trade_log a where a.app_id=? group by a.trade_code order by nm desc) t) tt where  tt.nn <=9) "+
" select distd.* from (select distinct( atl.trade_name)  trade_name,temp.trade_code,temp.nn,temp.nm from app_trade_log atl,tmp temp where atl.trade_code= temp.trade_code order by temp.nn ) distd");
			ps.setString(1, appId);
			rs = ps.executeQuery();
			while (rs.next()) {
				AppTradeLog dc = new AppTradeLog();
				//dc.setAppId(rs.getString("APP_ID"));
				dc.setTradeCode(rs.getString("trade_code"));
				dc.setTradeName(rs.getString("trade_name"));
				dc.setAmount(Integer.parseInt(rs.getString("nm")));
				dcList.add(dc);
			}
		} catch (SQLException e) {
			throw new SQLException("queryWithBytradeCode is Wrong!"
					+ e.getMessage());
		} finally {
			close(rs, null, ps);
		}
		return dcList;
	}
	
	/**
	 * 查询指定工程下总交易数量
	 * 
	 * @param appId
	 * @return
	 * @throws SQLException
	 */
	public int querySumWithBytradeCode(String appId)
			throws SQLException {
		PreparedStatement ps = null;
		Connection conn = ConnectionManager.getConnection();
		ResultSet rs = null;
		int sum = 0;
		try {
			ps = conn.prepareStatement("SELECT COUNT(trade_code) amount FROM app_trade_log WHERE APP_ID=? ");
			ps.setString(1, appId);
			rs = ps.executeQuery();
			if (rs.next()) {
				sum = Integer.parseInt(rs.getString("amount"));
			}
		} catch (SQLException e) {
			throw new SQLException("querySumWithBytradeCode is Wrong!"
					+ e.getMessage());
		} finally {
			close(rs, null, ps);
		}
		return sum;
	}
	
	/**
	 * 查询指定工程下每日用户访问量
	 * 
	 * @param appId date    适用于MYSQL数据库
	 * @return
	 * @throws SQLException
	 */
	public List<AppTradeLog> queryWithByStartTime(String appId, String date)
			throws SQLException {
		List<AppTradeLog> dcList = new ArrayList<AppTradeLog>();
		PreparedStatement ps = null;
		Connection conn = ConnectionManager.getConnection();
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("SELECT APP_ID,SUBSTRING(START_TIME,1,10) time,COUNT(START_TIME) count FROM app_trade_log " + 
					" WHERE APP_ID=? AND SUBSTRING(START_TIME,1,10)>=? GROUP BY SUBSTRING(START_TIME,1,10) ORDER BY START_TIME ASC ");
			ps.setString(1, appId);
			ps.setString(2, date);
			rs = ps.executeQuery();
			while (rs.next()) {
				AppTradeLog dc = new AppTradeLog();
				dc.setAppId(rs.getString("APP_ID"));
				dc.setStartTime(rs.getString("time"));
				dc.setAmount(Integer.parseInt(rs.getString("count")));
				dcList.add(dc);
			}
		} catch (SQLException e) {
			throw new SQLException("queryWithByStartTime is Wrong!"
					+ e.getMessage());
		} finally {
			close(rs, null, ps);
		}
		return dcList;
	}
	//适用Oracle数据库
	public List<AppTradeLog> queryWithByStartTimeOracl(String appId, String date)
			throws SQLException {
		List<AppTradeLog> dcList = new ArrayList<AppTradeLog>();
		PreparedStatement ps = null;
		Connection conn = ConnectionManager.getConnection();
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("SELECT SUBSTR(a.START_TIME,1,10) stime,count(SUBSTR(a.START_TIME,1,10)) nm FROM app_trade_log a "+
"where a.app_id=? and SUBSTR(a.START_TIME,1,10) >=? group by SUBSTR(a.START_TIME,1,10) order by stime asc");
			ps.setString(1, appId);
			ps.setString(2, date);
			rs = ps.executeQuery();
			while (rs.next()) {
				AppTradeLog dc = new AppTradeLog();
				//dc.setAppId(rs.getString("APP_ID"));
				dc.setStartTime(rs.getString("STIME"));
				dc.setAmount(Integer.parseInt(rs.getString("NM")));
				dcList.add(dc);
			}
		} catch (SQLException e) {
			throw new SQLException("queryWithByStartTime is Wrong!"
					+ e.getMessage());
		} finally {
			close(rs, null, ps);
		}
		return dcList;
	}
	
	/**
	 * 查询指定工程下每日用户访问最大量
	 * 
	 * @param appId date
	 * @return
	 * @throws SQLException
	 */
	public int querySumWithByStartTime(String appId, String date)
			throws SQLException {
		PreparedStatement ps = null;
		Connection conn = ConnectionManager.getConnection();
		ResultSet rs = null;
		int max = 0;
		try {
			ps = conn.prepareStatement("SELECT MAX(tab.ct) maxCount from (SELECT COUNT(START_TIME) ct FROM app_trade_log "+
					" WHERE APP_ID=? AND SUBSTRING(START_TIME,1,10)>=? GROUP BY SUBSTRING(START_TIME,1,10)) tab");
			ps.setString(1, appId);
			ps.setString(2, date);
			rs = ps.executeQuery();
			if (rs.next()) {
				max = Integer.parseInt(rs.getString("maxCount"));
			}
		} catch (SQLException e) {
			throw new SQLException("querySumWithByStartTime is Wrong!"
					+ e.getMessage());
		} finally {
			close(rs, null, ps);
		}
		return max;
	}
	//适用Oracle数据库
	public int querySumWithByStartTimeOracl(String appId, String date)
			throws SQLException {
		PreparedStatement ps = null;
		Connection conn = ConnectionManager.getConnection();
		ResultSet rs = null;
		int max = 0;
		try {
			ps = conn.prepareStatement("SELECT MAX(tab.amount) maxCount from (SELECT COUNT(START_TIME) amount FROM app_trade_log "+
    " WHERE APP_ID=? AND SUBSTR(START_TIME,1,10)>=? GROUP BY SUBSTR(START_TIME,1,10)) tab");
			ps.setString(1, appId);
			ps.setString(2, date);
			rs = ps.executeQuery();
			if (rs.next()) {
				max = Integer.parseInt(rs.getString("maxCount"));
			}
		} catch (SQLException e) {
			throw new SQLException("querySumWithByStartTime is Wrong!"
					+ e.getMessage());
		} finally {
			close(rs, null, ps);
		}
		return max;
	}
	
	/**
	 * 查询指定工程下手机系统安装比例
	 * 
	 * @param appId
	 * @return
	 * @throws SQLException
	 */
	public List<AppDeviceRegister> queryWithByDeviceType(String appId)
			throws SQLException {
		List<AppDeviceRegister> dcList = new ArrayList<AppDeviceRegister>();
		PreparedStatement ps = null;
		Connection conn = ConnectionManager.getConnection();
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("SELECT DEVICE_TYPE,COUNT(DEVICE_TYPE) amount FROM app_device_register WHERE APP_ID=? GROUP BY DEVICE_TYPE ");
			ps.setString(1, appId);
			rs = ps.executeQuery();
			while (rs.next()) {
				AppDeviceRegister dc = new AppDeviceRegister();
				//dc.setAppId(rs.getString("APP_ID"));
				dc.setDeviceType(rs.getString("DEVICE_TYPE"));
				dc.setAmount(Integer.parseInt(rs.getString("amount")));
				dcList.add(dc);
			}
		} catch (SQLException e) {
			throw new SQLException("queryWithByDeviceType is Wrong!"
					+ e.getMessage());
		} finally {
			close(rs, null, ps);
		}
		return dcList;
	}
	
	/**
	 * 查询指定工程下手机型号系统总数量
	 * 
	 * @param appId
	 * @return
	 * @throws SQLException
	 */
	public int querySumWithByDeviceType(String appId)
			throws SQLException {
		PreparedStatement ps = null;
		Connection conn = ConnectionManager.getConnection();
		ResultSet rs = null;
		int sum = 0;
		try {
			ps = conn.prepareStatement("SELECT COUNT(DEVICE_TYPE) amount FROM app_device_register WHERE APP_ID=? ");
			ps.setString(1, appId);
			rs = ps.executeQuery();
			if (rs.next()) {
				sum = Integer.parseInt(rs.getString("amount"));
			}
		} catch (SQLException e) {
			throw new SQLException("querySumWithByDeviceType is Wrong!"
					+ e.getMessage());
		} finally {
			close(rs, null, ps);
		}
		return sum;
	}
	
	/**
	 * 查询指定工程下手机型号安装比例
	 * 
	 * @param appId
	 * @return
	 * @throws SQLException
	 */
	public List<AppDeviceRegister> queryWithByDeviceModel(String appId)
			throws SQLException {
		List<AppDeviceRegister> dcList = new ArrayList<AppDeviceRegister>();
		PreparedStatement ps = null;
		Connection conn = ConnectionManager.getConnection();
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("SELECT DEVICE_MODEL,COUNT(DEVICE_MODEL) amount FROM app_device_register WHERE APP_ID=? GROUP BY DEVICE_MODEL ");
			ps.setString(1, appId);
			rs = ps.executeQuery();
			while (rs.next()) {
				AppDeviceRegister dc = new AppDeviceRegister();
				//dc.setAppId(rs.getString("APP_ID"));
				dc.setDeviceModel(rs.getString("DEVICE_MODEL"));
				dc.setAmount(Integer.parseInt(rs.getString("amount")));
				dcList.add(dc);
			}
		} catch (SQLException e) {
			throw new SQLException("queryWithByDeviceMODEL is Wrong!"
					+ e.getMessage());
		} finally {
			close(rs, null, ps);
		}
		return dcList;
	}
	
	/**
	 * 查询指定工程下手机型号安装总数量
	 * 
	 * @param appId
	 * @return
	 * @throws SQLException
	 */
	public int querySumWithByDeviceModel(String appId)
			throws SQLException {
		PreparedStatement ps = null;
		Connection conn = ConnectionManager.getConnection();
		ResultSet rs = null;
		int sum = 0;
		try {
			ps = conn.prepareStatement("SELECT COUNT(DEVICE_MODEL) amount FROM app_device_register WHERE APP_ID=? ");
			ps.setString(1, appId);
			rs = ps.executeQuery();
			if (rs.next()) {
				sum = Integer.parseInt(rs.getString("amount"));
			}
		} catch (SQLException e) {
			throw new SQLException("querySumWithByDeviceMODEL is Wrong!"
					+ e.getMessage());
		} finally {
			close(rs, null, ps);
		}
		return sum;
	}

	/**
	 * 关闭资源
	 * 
	 * @param theRs
	 * @param theStmt
	 * @param thePs
	 */
	protected void close(ResultSet theRs, Statement theStmt,
			PreparedStatement thePs) {
		try {
			if (theRs != null)
				theRs.close();
			if (theStmt != null)
				theStmt.close();
			if (thePs != null)
				thePs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 关闭资源
	 * 
	 * @param theRs
	 * @param theStmt
	 * @param thePs
	 */
	protected void close1(Connection conn, PreparedStatement thePs) {
		try {
			if (conn != null)
				conn.close();
			if (thePs != null)
				thePs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 关闭资源
	 * 
	 * @param theRs
	 * @param theStmt
	 * @param thePs
	 */
	protected void close2(ResultSet theRs, Connection conn,
			PreparedStatement thePs) {
		try {
			if (theRs != null)
				theRs.close();
			if (conn != null)
				conn.close();
			if (thePs != null)
				thePs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
